﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using CubeDAL;
using SysInfo;
using System.IO;

namespace importExcel
{
    public class DataImporter
    {
        private string path;
        private string err_path;
        private Encoding encoding = Encoding.UTF8;
        private MySQLWrapper wrapper;
        private int[] MAX_COLUMN = new int[]{129,115,81};
        private int type;
        private int[][] skipCols;
        private int[][] dateCols;
        private int[][] numCols;
        private static string[] tableName={"yuqing","rumor","weibo_event"};
        private static int MAX_COLUMN_CELEBRITY = 22;
        private string sqlDir = "d:\\";
        /* public DataImporter(string data_path,int p=0)
        {
            type = p;
            path = data_path;
            err_path = data_path + ".err.txt";
            Initial();
        }*/

        public DataImporter(string data_path,int p=2,String data_encoding="gb2312")
        {
            type = p;
            path = data_path;
            encoding = Encoding.GetEncoding(data_encoding);
            err_path = data_path + ".err.txt";
            Initial();
        }

        /// <summary>
        /// Executing importing data
        /// </summary>
        /// <returns>[success number, failure number] pair</returns>
        public KeyValuePair<int, int> Execute()
        {
            int data_count = 1;
            int success = 0, fail = 0;

            StreamReader sr = new StreamReader(path, encoding);
            StreamWriter sw_err = new StreamWriter(err_path);

            int[] skipCol = skipCols[type];//需要跳过的列
            int[] dateCol = dateCols[type];//需要转化时间的列
            int[] numCol = numCols[type];

            string line, sql;
            string[] arr_str;
            bool success_line;

            bool celebritySuc=true;
            String nextNo="1";

            if (type == 2) //celebrity
            {
                String celebrityLine;
                String[] celebrityArr;
                if (!sr.EndOfStream)
                {
                    celebrityLine = sr.ReadLine();
                    celebrityArr = celebrityLine.Split('\t');
                    int[] dateColl = new int[] { 8,12,21};
                    int[] numColl = new int[] { 9,10,13,15,16,17,19};
                    String getNoSQL="select MAX(id) from `weibo_celebrity`";
                    String[] output=new String[]{"MAX(id)"};
                    List<List<String>> result;
                    
                    if ((result=wrapper.Select(getNoSQL,output)).Count!=0)
                        nextNo=(getNumber(result[0][0])+1).ToString();
                    else nextNo="1";
                    if (nextNo=="") nextNo="1";
                    String celebritySQL = "insert into `weibo_celebrity` values(" + nextNo;
                    for (int i = 0; i < celebrityArr.Length && i < MAX_COLUMN_CELEBRITY; i++)
                    {
                        if (ExistIn(i, numColl))
                        {
                            celebrityArr[i] = String2Int(celebrityArr[i]);
                        }
                        // check date format
                        if (ExistIn(i, dateColl))
                        {
                            celebrityArr[i] = String2DateTime(celebrityArr[i]);
                            if (celebrityArr[i] == "-1")
                            {
                                //success_line = false;
                                //break;
                                celebrityArr[i] = "";
                            }
                        }
                        if (i == 18) celebrityArr[i]=celebrityArr[i].Replace("%", "");
                        if (celebrityArr[i] == null || celebrityArr[i].Trim() == "")
                            celebritySQL += ", null";
                        else
                            celebritySQL += ", '" + EscapeSQL(celebrityArr[i]) + "'";
                }
                celebritySQL += ");";

                celebritySuc = InsertSQL(celebritySQL);

                
                
                }



                
                if (!sr.EndOfStream) sr.ReadLine();
                if (!sr.EndOfStream) sr.ReadLine();
                if (!sr.EndOfStream) sr.ReadLine();
            }
            if (celebritySuc)
            {
                while (!sr.EndOfStream)
                {
                    line = sr.ReadLine();
                    arr_str = line.Split('\t');
                    success_line = true;
                    sql = "INSERT INTO `" + tableName[type] + "` VALUES (null";
                    if (type == 2) sql += "," + nextNo;
                    int count = 0;
                    for (int i = 0; i < arr_str.Length && i < MAX_COLUMN[type]; ++i)
                    {
                        // skip lines
                        if (ExistIn(i, skipCol))
                            continue;
                        if (ExistIn(i, numCol))
                        {
                            arr_str[i] = String2Int(arr_str[i]);
                        }
                        // check date format
                        if (ExistIn(i, dateCol))
                        {
                            arr_str[i] = String2DateTime(arr_str[i]);
                            if (arr_str[i] == "-1")
                            {
                                //success_line = false;
                                //break;
                                arr_str[i] = "";
                            }
                        }
                        // add to sql statement
                        if (arr_str[i] == null || arr_str[i].Trim() == "")
                            sql += ", null";
                        else
                            sql += ", '" + EscapeSQL(arr_str[i]) + "'";
                        ++count;
                    }
                    sql += ");";
                    if (!success_line)
                    {
                        sw_err.WriteLine(line);
                        ++fail;
                    }
                    else if (InsertSQL(sql))
                        ++success;
                    else
                    {
                        sw_err.WriteLine(line);
                        ++fail;
                    }
                    ++data_count;
                }
            }
            sr.Close();
            sw_err.Flush();
            sw_err.Close();
            return new KeyValuePair<int, int>(success, fail);
        }


        private bool ExistIn(int testNum, int[] set) 
        {
            foreach (int i in set)
                if (testNum == i)
                    return true;
            return false;
        }

        /// <summary>
        /// Change plain date into formatted string
        /// </summary>
        /// <param name="dateStr"></param>
        /// <returns>"-1" for error</returns>
        private String String2Int(String s)
        {
            char[] x = s.ToCharArray();
            String newstr = "";
            for (int j = 0; j < s.Length; j++)
            {
                if (x[j] >= '0' && x[j] <= '9')
                    newstr += x[j].ToString();
            }
            return newstr;
        }
        private string String2DateTime(String dateStr)
        {
            string year = "", month = "", day = "";
            int dateFormat = 1;
            dateStr.Replace("时", ":");
            dateStr.Replace("分", ":");
            dateStr.Replace("秒", "");
            //处理 YYYY年MM月DD日
            if (dateStr.Contains("年") && dateStr.Contains("月") && dateStr.Contains("日"))
            {
                year = dateStr.Substring(0, dateStr.IndexOf("年"));
                month = dateStr.Substring(dateStr.IndexOf("年") + 1, dateStr.IndexOf("月") - dateStr.IndexOf("年") - 1);
                day = dateStr.Substring(dateStr.IndexOf("月") + 1, dateStr.IndexOf("日") - dateStr.IndexOf("月") - 1);
                int i = dateStr.IndexOf("日") + 1;
                day += " ";
                bool flag = true;
                while (i < dateStr.Length && flag)
                    if ((dateStr[i] >= '0' && dateStr[i] <= '9') || (dateStr[i].Equals(':') || dateStr[i].Equals('：')))
                        i++;
                    else
                        flag = false;
                i = dateStr.IndexOf("日");
                while (flag && ++i < dateStr.Length)
                    if (dateStr[i].Equals('：'))
                        day += ':';
                    else
                        day += dateStr[i];
            }
            else if (dateStr.Contains("/") || dateStr.Contains("-"))
            {
                //处理 YYYY/MM/DD/ time
                char[] septor = { '/' };
                if (dateStr.Contains("-"))
                    septor[0] = '-';
                string[] date = dateStr.Split(septor);
                if (date.Length == 3)
                {
                    year = date[0];
                    month = date[1];
                    day = date[2];
                }
                else dateFormat = 0;
            }
            else
                dateFormat = 0;//valid date format

            //return value
            if (dateFormat == 1)
            {
                return year + "-" + month + "-" + day;
            }
            else
                return "-1";

        }

        private string EscapeSQL(string str)
        {
            return str.Replace("'", "''");
        }

        private bool InsertSQL(string sql)
        {
            try
            {
                int res_code = wrapper.Insert(sql);
                return (res_code != -1)? true: false;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return false;
            }
        }

        private int getNumber(String x)
        {
            int num = 0;
            for (int i = 0; i < x.Length; i++)
            {
                num = num * 10 + x[i] - '0';
            }
            return num;
        }

        private void Initial()
        {
            skipCols = new int[3][];
            skipCols[0]=new int[]{6,15,17};
            skipCols[1]=new int[]{5,15,17};
            skipCols[2] = new int[] { };
            dateCols = new int[3][];
            dateCols[0] = new int[] {5,71,72 };
            dateCols[1] = new int[] {71,72};
            dateCols[2] = new int[] {12,48,62};
            numCols = new int[3][];
            numCols[0] = new int[] { 2, 3, 4, 7, 8, 10, 11, 12, 14, 16, 18 ,40,63,64,73,74,75,76,86,87,100};
            numCols[1] = new int[] { 2,3,4,6,7,9,10,11,12,14,16,18,60,63,64,65,66,67,74,76,92,97,99,101,103,107,108,111,112,113,114};
            numCols[2] = new int[] { 0,6,9,11,14,15,16,17,22,23,25,28,29,30,36,39,41,43,45,46,47,51,54,56,58,59,60,61,65,68,70,72,73,74,75,76,78};
            wrapper = new MySQLWrapper(Constant.DB_SERVER, Constant.DB_DATABASE, Constant.DB_USER, Constant.DB_PASSWORD);
        }
    }
}
